﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class StatisticBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public StatisticBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }


        public DataTable GetDetail(string w,int s,int n)
        {
            List<CheckOut> list = new List<CheckOut>();
            string sql = "select name 桌位,sum 小计,final_cash 实收, sub_sum 优惠,pc_sum 赔偿,cash_sum 现金支付,djq_sum 代金券,tgq_sum 团购券,xyk_sum 信用卡,hyk_sum 会员卡,time 时间,checkout_num 结账单号,order_num 点餐单号 from dc_checkout dc join dc_tab dt on dc.tab_num=dt.tab_num where " + w + " order by time DESC limit " + s + "," + n;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];               
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetSubSum(string where)
        {
            string sql = "select round(sum(sub_sum),1) sum from dc_checkout where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                    return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetFinalSum(string where)
        {
            string sql = "select round(sum(final_cash),1) from dc_checkout where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                    return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetSum(string where)
        {
            string sql = "select round(sum(sum),1) from dc_checkout_ext where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                    return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetGzSum(string where)
        {
            string sql = "select round(sum(sum),1) from dc_checkout_ext where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetHisReturnedSum(string where)
        {
            string sql = "select round(sum(count*price),1) from dc_hisreturned where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetCheckOutedSum(string f,string where)
        {
            string sql = "select round(sum(" + f + "),1) from dc_checkout where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                    return float.Parse(reader[0]+"");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public int GetCheckOutedNum(string where)
        {
            string sql = "select count(id) num from dc_checkout where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                    return Int32.Parse(reader[0]+"");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public int GetNotCheckOutedNum(string where)
        {
            string sql = "select count(id) from dc_order where haschecked=0 and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return Int32.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetNotCheckOutedSum(string where)
        {
            string sql = "SELECT ROUND(SUM(price*number),1) FROM dc_orders dos JOIN dc_order do ON dos.`order_num`=do.`order_num` WHERE haschecked=0 and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public int GetNotCheckOutedReturnedSum(string where)
        {
            string sql = "SELECT ROUND(SUM(price*count),1) FROM dc_returned dr JOIN dc_order `do` ON dr.`order_num`=do.`order_num` WHERE haschecked=0 and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return Int32.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }

        public int GetHisTgNum(string where)
        {
            string sql = "select count(id) from dc_hisorder where haschecked=1 and together_name!='' and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return Int32.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public float GetHisTgSum(string where)
        {
            string sql = "select round(sum(together_price),1) from dc_hisorder where " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
        public DataTable GetHisAllTgInfo(string where)
        {
            string sql = "select together_name 套餐名,count(id) as 数量,together_price 单价（元）,round(together_price*count(id),1) as 总额（元） from dc_hisorder where haschecked=1 and together_name!='' and  " + where + "  group by together_name";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myConn);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                dbc.close();
            }
        }

        public float GetHisOrdersSum(string where)
        {
            string sql = "select round(sum(price*number),1) from dc_hisorders dhs,dc_hisorder dh where dhs.order_num=dh.order_num and " + where;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    if (reader[0].ToString() != "")
                        return float.Parse(reader[0] + "");
                }
                return 0;
            }
            finally
            {
                dbc.close();
            }
        }
    }
}
